package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ReviewerFormDelete;
import forms.ReviewerFormInsert;

public class AdminModel {

	private static String selectReviewerStr = "SELECT user_name FROM user_roles WHERE role = \'reviewer\' ORDER BY id";
	private static String selectDisciplinesStr = "SELECT discipline FROM disciplines";
	private static String selectSpecializationsStr = "SELECT specialization FROM specializations";

	private static String selectDisciplineDistinctStr = "SELECT DISTINCT first_name FROM Students s, Disciplines dp, Degrees deg WHERE deg.student=s.id AND deg.discipline=" +
	"dp.id AND dp.discipline = ?";
	private static String selectSpecializationDistinctStr = "SELECT DISTINCT first_name FROM Students s, Specializations sp WHERE s.specialization=sp.id" +
	" AND sp.specialization = ?";	
	private static String selectFinishedAppReviewsStr = "SELECT COUNT(*) FROM App_reviews app, Users u WHERE app.reviewer=u.id" + 
	" AND app.finished=true AND u.user_name = ?";
	private static String selectUnfinishedAppReviewsStr = "SELECT COUNT(*) FROM App_reviews app, Users u WHERE app.reviewer=u.id" + 
	" AND app.finished=false AND u.user_name = ?";
	
	private static String selectApplicantByDisciplineStr = "SELECT DISTINCT s.id, s.first_name, s.middle_name," + 
	" s.last_name, s.admission_status FROM Students s, Disciplines dp, " +
	"Degrees deg WHERE deg.student=s.id AND deg.discipline=dp.id AND dp.discipline=?";
	private static String selectDegreeByDisciplineStr = "SELECT gpa FROM Degrees WHERE student=?";
	
	private static String selectApplicantBySpecializationStr = "SELECT DISTINCT s.id, s.first_name, s.middle_name," + 
	" s.last_name , s.admission_status FROM students s, Specializations sp WHERE s.specialization=sp.id AND sp.specialization=?";
	private static String selectDegreeBySpecializationStr = "SELECT gpa FROM Degrees WHERE student=?";
	
	private static String selectApplicantByReviewerStr = "SELECT DISTINCT s.id, s.first_name, s.middle_name," + 
	" s.last_name, s.admission_status FROM Students s, App_reviews app, " +
	"Users u WHERE app.application=s.id AND app.reviewer=u.id AND u.user_name=? AND app.finished=?";
	private static String selectDegreeByReviewerStr = "SELECT gpa FROM Degrees WHERE student=?";
	
	private static String insertUserStr = "INSERT INTO users (user_name, password) VALUES (?, md5(?))";
	private static String insertUserRoleStr = "INSERT INTO user_roles (user_name, role) VALUES (?, ?)";
	private static String updateStatusStr = "UPDATE students SET admission_status = ? WHERE id = ?";
	private static String deleteUserStr = "DELETE FROM users WHERE user_name = ?";
	private static String deleteUserRoleStr = "DELETE FROM user_roles WHERE user_name = ?";
	
	private static String getAppReviewCount = "SELECT count(*) FROM app_reviews";
	
	// Helper code to help map reviewers to applications
	private static String getAllApplicationIDs = "SELECT id FROM Students";
	private static String getAllReviewerIDs = "SELECT u.id FROM Users u, User_roles ur WHERE ur.role=\'reviewer\' AND ur.user_name=u.user_name";
	private static String insertAppReviewStr = "INSERT INTO App_reviews (application, reviewer, finished) VALUES (?, ?, false)";

	public static CachedRowSet getAllReviewers() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerStr);

			// Use the created statement to SELECT
			// all the reviewers FROM the users table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			allReviewers.close();
			pStmt.close();
			conn.close();

			return crsReviewers;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static int getFinishedAppReviews(String reviewer) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectFinishedAppReviewsStr);
			pStmt.setString(1, reviewer);

			ResultSet allAppReviews = pStmt.executeQuery();

			int result = -1;
			while(allAppReviews.next())
				result = allAppReviews.getInt("count");
			

			allAppReviews.close();
			pStmt.close();
			conn.close();

			return result;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static int getUnfinishedAppReviews(String reviewer) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectUnfinishedAppReviewsStr);
			pStmt.setString(1, reviewer);

			ResultSet allAppReviews = pStmt.executeQuery();

			int result = -1;
			while(allAppReviews.next())
				result = allAppReviews.getInt("count");
			

			allAppReviews.close();
			pStmt.close();
			conn.close();

			return result;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	
	public static CachedRowSet getAllDisciplines() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDisciplinesStr);

			// Use the created statement to SELECT
			// all the reviewers FROM the users table.
			ResultSet allDisciplines = pStmt.executeQuery();

			CachedRowSet crsDisciplines = new CachedRowSetImpl();
			crsDisciplines.populate(allDisciplines);

			allDisciplines.close();
			pStmt.close();
			conn.close();

			return crsDisciplines;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}	

	public static Vector<String> getDisciplineValues() throws DbException {

		try {
			
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDisciplineDistinctStr);
			// Get all the disciplines from another method
			CachedRowSet disciplines = getAllDisciplines();
			
			Vector<String> ret = new Vector<String>();
			
			while(disciplines.next())
			{
				int counter = 0;
				pStmt.setString(1, disciplines.getString("discipline"));
				ResultSet allDisciplines = pStmt.executeQuery();

				CachedRowSet crsDisciplines = new CachedRowSetImpl();
				crsDisciplines.populate(allDisciplines);
				// count up the number of applications for the given discipline
				while(crsDisciplines.next())
				{
					counter++;
				}
				ret.add(Integer.toString(counter));
				
			}

			disciplines.close();
			pStmt.close();
			conn.close();

			return ret;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}	

	public static CachedRowSet getAllSpecializations() throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpecializationsStr);

			// Use the created statement to SELECT
			// all the reviewers FROM the users table.
			ResultSet allSpecializations = pStmt.executeQuery();

			CachedRowSet crsSpecializations = new CachedRowSetImpl();
			crsSpecializations.populate(allSpecializations);

			allSpecializations.close();
			pStmt.close();
			conn.close();

			return crsSpecializations;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}	

	public static Vector<String> getSpecializationValues() throws DbException {

		try {
			
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectSpecializationDistinctStr);
			// Get all the specializations from another method
			CachedRowSet specializations = getAllSpecializations();
			
			Vector<String> ret = new Vector<String>();
			
			while(specializations.next())
			{
				int counter = 0;
				pStmt.setString(1, specializations.getString("specialization"));
				ResultSet allSpecializations = pStmt.executeQuery();
				
				CachedRowSet crsSpecializations = new CachedRowSetImpl();
				crsSpecializations.populate(allSpecializations);
				// count up the number of applications for the given discipline
				while(crsSpecializations.next())
				{
					counter++;
				}
				ret.add(Integer.toString(counter));
				
			}

			specializations.close();
			pStmt.close();
			conn.close();

			return ret;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}	
	
	public static int getAllReviewersCount() throws DbException {
		int counter = 0;
		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectReviewerStr);

			// Use the created statement to SELECT
			// all the reviewers attributes FROM the users table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);
			// count up the number of reviewers using the counter
			while(crsReviewers.next())
			{
				counter++;
			}
			allReviewers.close();
			pStmt.close();
			conn.close();

			return counter;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getApplicationsByDiscipline(String discipline) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectApplicantByDisciplineStr);
			pStmt.setString(1, discipline);
			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allApplicants = pStmt.executeQuery();

			CachedRowSet crsApplicants = new CachedRowSetImpl();
			crsApplicants.populate(allApplicants);

			allApplicants.close();
			pStmt.close();
			conn.close();

			return crsApplicants;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getDegreesByDiscipline(int student_index) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDegreeByDisciplineStr);
			pStmt.setInt(1, student_index);

			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allDegrees = pStmt.executeQuery();

			CachedRowSet crsDegrees = new CachedRowSetImpl();
			crsDegrees.populate(allDegrees);

			allDegrees.close();
			pStmt.close();
			conn.close();

			return crsDegrees;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getApplicationsBySpecialization(String specialization) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectApplicantBySpecializationStr);
			pStmt.setString(1, specialization);
			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allApplicants = pStmt.executeQuery();

			CachedRowSet crsApplicants = new CachedRowSetImpl();
			crsApplicants.populate(allApplicants);

			allApplicants.close();
			pStmt.close();
			conn.close();

			return crsApplicants;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getDegreesBySpecialization(int student_index) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDegreeBySpecializationStr);
			pStmt.setInt(1, student_index);

			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allDegrees = pStmt.executeQuery();

			CachedRowSet crsDegrees = new CachedRowSetImpl();
			crsDegrees.populate(allDegrees);

			allDegrees.close();
			pStmt.close();
			conn.close();

			return crsDegrees;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getApplicationsByReviewer(String reviewer, String finished) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectApplicantByReviewerStr);
			pStmt.setString(1, reviewer);
			pStmt.setBoolean(2, Boolean.parseBoolean(finished));
			System.err.println("Reviewer: " + reviewer);
			System.err.println("Finished? " + finished);
			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allApplicants = pStmt.executeQuery();

			CachedRowSet crsApplicants = new CachedRowSetImpl();
			crsApplicants.populate(allApplicants);

			allApplicants.close();
			pStmt.close();
			conn.close();

			return crsApplicants;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static CachedRowSet getDegreesByReviewer(int student_index) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectDegreeByReviewerStr);
			pStmt.setInt(1, student_index);

			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet allDegrees = pStmt.executeQuery();

			CachedRowSet crsDegrees = new CachedRowSetImpl();
			crsDegrees.populate(allDegrees);

			allDegrees.close();
			pStmt.close();
			conn.close();

			return crsDegrees;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	
	public static int getAppReviewCount() throws DbException {

		try {
			int result = 0;
			Connection conn = DbConnectionPool.getConnection();
			
			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(getAppReviewCount);
			
			// Use the created statement to SELECT
			// all the students FROM the student table where discipline is stated.
			ResultSet count = pStmt.executeQuery();

			CachedRowSet crsCount = new CachedRowSetImpl();
			crsCount.populate(count);
			while(crsCount.next())
				result = crsCount.getInt("count");

			count.close();
			pStmt.close();
			conn.close();

			return result;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
/*	public static CachedRowSet getResumeOfStudent(int studentID)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			PreparedStatement pStmt = conn.prepareStatement(selectResumeStr);

			pStmt.setInt(1, studentID);
			ResultSet rs = pStmt.executeQuery();

			CachedRowSet crsResume = new CachedRowSetImpl();
			crsResume.populate(rs);

			rs.close();
			pStmt.close();
			conn.close();

			return crsResume;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}

	}
*/
	public static void insertReviewer(ReviewerFormInsert reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			// Create the prepared statement and use it to
			// INSERT user values INTO the users table.
			PreparedStatement pStmt = conn.prepareStatement(insertUserStr);

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());

			pStmt.executeUpdate();
			System.err.println("User inserted into user table");
			
			// Close statement, then reopen it to insert into user_roles table.
			pStmt.close();
			pStmt = conn.prepareStatement(insertUserRoleStr);
			
			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, "reviewer");
			
			pStmt.executeUpdate();
			System.err.println("User role also added to the user_role table!");
			
			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void updateApplicationStatus(int user_id, String updatedStatus)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the prepared statement and use it to
			// UPDATE student values in the Students table.
			PreparedStatement pStmt = conn.prepareStatement(updateStatusStr);

			pStmt.setString(1, updatedStatus);
			pStmt.setInt(2, user_id);
			pStmt.executeUpdate();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	public static void removeReviewer(ReviewerFormDelete reviewer)
			throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);
			// Create the prepared statement and use it to
			// DELETE students FROM the users table.
			PreparedStatement pStmt = conn.prepareStatement(deleteUserStr);

			pStmt.setString(1, reviewer.getName());
			pStmt.executeUpdate();
			System.err.println("User removed into user table");
			
			// Close statement, then reopen it to insert into user_roles table.
			pStmt.close();
			pStmt = conn.prepareStatement(deleteUserRoleStr);
			
			pStmt.setString(1, reviewer.getName());
			
			pStmt.executeUpdate();
			System.err.println("User role also deleted to the user_role table!");
			
	
			// Commit transaction
			conn.commit();

			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void mapReviewersToApplications()
	throws DbException {
		
		try {
			Connection conn = DbConnectionPool.getConnection();
			conn.setAutoCommit(false);

			PreparedStatement pStmt = conn.prepareStatement(getAllApplicationIDs);

			ResultSet allApplications = pStmt.executeQuery();

			CachedRowSet crsApplications = new CachedRowSetImpl();
			crsApplications.populate(allApplications);

			allApplications.close();
			pStmt.close();
			
			pStmt = conn.prepareStatement(getAllReviewerIDs);
			
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			pStmt.close();
			pStmt = conn.prepareStatement(insertAppReviewStr);
			
			int counter = 1;
			while(crsApplications.next())
			{
				crsReviewers.absolute(counter);
				int reviewer_id = crsReviewers.getInt("id");
				int applicant_id = crsApplications.getInt("id");
				
				pStmt.setInt(1, applicant_id);
				pStmt.setInt(2, reviewer_id);
				
				pStmt.executeUpdate();
				System.err.println("Application Review submitted!");
				counter++;
				if(counter > crsReviewers.size())
					counter = 1;
			}

			conn.commit();
			
			allReviewers.close();			
			conn.close();
		
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}
